package main.reports;

import main.SQL.MsSQLConnect;
import main.Trees.ProjectNode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class XLSSetup extends Thread {
    private HashMap<String, CellStyle> styles;
    private HashMap<String, Double> allValuesTask, allValuesSubBlock, allValuesBlock, allValuesSector, allValuesSection, allValuesSetup;
    private HashSet<String> hashSetSection;
    private HashMap<String, HashSet<String>> sectionSetup, setupSector, sectorBlock, blockSubBlock, blockTask;
    private String pathXLSFile;
    private Connection connSQL;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private int rownum, curReport;
    private ArrayList<ProjectNode> projectList;
    private String sectionSelect, type;
    private BusyDialog busyDlg;
    private ArrayList<String> workType, errorList;

    public XLSSetup(ArrayList<ProjectNode> projectList, Connection connSQL, String sectionSelect, String type) {
        this.projectList = projectList;
        this.connSQL = connSQL;
        this.sectionSelect = sectionSelect;
        this.type = type;
    }

    //region Запуск потока, вызов FileChooser
    public void run() {
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Выберите путь для сохранения файла");
        fileChooser.setApproveButtonText("Сохранить");
        File fle = new File("Матричный отчет - " + projectList.get(0).getShortName().replace("/", "-").replace("\\", "-"));
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
            generateXLS();
        }
    }
    //endregion

    //region Открытие/закрытие соединения с БД. Цикличная генерация отчетов
    private void generateXLS() {
        busyDlg = new BusyDialog("Экспорт матричных отчётов", "Выполняется SQL-запрос");
        busyDlg.start();

        connSQL = MsSQLConnect.ConnectDB("Factage");
        fillRows();

        if (errorList.size() < 1) {
            for (int i = 1; i < 4; i++) {
                busyDlg.setDescription("Создание " + i + " отчёта");
                curReport = i;
                createReport();
            }

            try {
                connSQL.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            busyDlg.setDescription("Матричные отчёты \r\n готовы");
        } else {

            busyDlg.setDescription("Работа " + errorList.get(0) + " неверно прокодирована.");
        }

        busyDlg.stopBusy();
    }
    //endregion

    //region Вызов хранимой процедуры и заполнение массивов данных
    private void fillRows() {

        sectionSetup = new HashMap<>();
        setupSector = new HashMap<>();
        sectorBlock = new HashMap<>();
        blockSubBlock = new HashMap<>();
        blockTask = new HashMap<>();
        allValuesTask = new HashMap<>();
        allValuesSubBlock = new HashMap<>();
        allValuesBlock = new HashMap<>();
        allValuesSector = new HashMap<>();
        allValuesSection = new HashMap<>();
        allValuesSetup = new HashMap<>();
        hashSetSection = new HashSet<>();
        HashSet<String> hashSetWorkType = new HashSet<>();
        errorList = new ArrayList<>();


        try {
            CallableStatement stmt;
            ResultSet rs;

            for (ProjectNode cur : projectList) {
                HashMap<Integer, MatRow> matHash = new HashMap<>();

                stmt = connSQL.prepareCall("{call getTaskReport(?, ?, ?)}");
                stmt.setString(1, cur.getDbName());
                stmt.setInt(2, cur.getId());
                stmt.setString(3, sectionSelect);

                rs = stmt.executeQuery();
                busyDlg.setDescription("Заполнение массива работ");
                while (rs.next()) {
                    MatRow newMat = new MatRow(rs.getInt("task_id"), rs.getString("task_code"), rs.getString("task_name"), rs.getString("proj_name"), rs.getString("section_code_desc"), rs.getString("sector_code_desc"), rs.getString("block_code_desc"), rs.getString("subblock_code_descr"), rs.getString("act_code_npz_descr"), rs.getString("act_code_value"), rs.getDouble("task_complete_pct"));
                    matHash.put(newMat.getTaskId(), newMat);
                }

                stmt = connSQL.prepareCall("{call getLinkReport(?, ?)}");
                stmt.setString(1, cur.getDbName());
                stmt.setInt(2, cur.getId());

                rs = stmt.executeQuery();

                while (rs.next()) {

                    if (!matHash.containsKey(rs.getInt("task_id"))) {
                        continue;
                    }

                    MatRow tempRow = matHash.get(rs.getInt("task_id"));
                    Double pv_vol = rs.getDouble("pv_vol");
                    Double t_vol = rs.getDouble("t_vol");
                    int virtual = rs.getInt("virtual");

                    if (virtual == 1) {
                        tempRow.setVirtual(true);
                        Double pvFactVirtual = new BigDecimal(tempRow.getPvFactVirtual()).add(new BigDecimal(pv_vol).multiply(new BigDecimal(tempRow.getPercent()))).doubleValue();
                        Double pvPlanVirtual = new BigDecimal(tempRow.getPvPlanVirtual()).add(new BigDecimal(pv_vol)).doubleValue();
                        Double tFactVirtual = new BigDecimal(tempRow.gettFactVirtual()).add(new BigDecimal(t_vol).multiply(new BigDecimal(tempRow.getPercent()))).doubleValue();
                        Double tPlanVirtual = new BigDecimal(tempRow.gettPlanVirtual()).add(new BigDecimal(t_vol)).doubleValue();
                        if (tempRow.getPv().equals("PV35") || tempRow.getPv().equals("PV36") || (!tempRow.getPv().contains("PV3") && !tempRow.getPv().contains("PV4"))) {
                            tempRow.setPvFactVirtual(new BigDecimal(pvFactVirtual).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                            tempRow.setPvPlanVirtual(new BigDecimal(pvPlanVirtual).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                        }
                        tempRow.settFactVirtual(new BigDecimal(tFactVirtual).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                        tempRow.settPlanVirtual(new BigDecimal(tPlanVirtual).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                    } else {
                        Double pvFact = new BigDecimal(tempRow.getPvFact()).add(new BigDecimal(pv_vol).multiply(new BigDecimal(tempRow.getPercent()))).doubleValue();
                        Double pvPlan = new BigDecimal(tempRow.getPvPlan()).add(new BigDecimal(pv_vol)).doubleValue();
                        Double tFact = new BigDecimal(tempRow.gettFact()).add(new BigDecimal(t_vol).multiply(new BigDecimal(tempRow.getPercent()))).doubleValue();
                        Double tPlan = new BigDecimal(tempRow.gettPlan()).add(new BigDecimal(t_vol)).doubleValue();

                        if (tempRow.getPv().equals("PV35") || tempRow.getPv().equals("PV36") || (!tempRow.getPv().contains("PV3") && !tempRow.getPv().contains("PV4"))) {
                            tempRow.setPvFact(new BigDecimal(pvFact).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                            tempRow.setPvPlan(new BigDecimal(pvPlan).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                        }
                        tempRow.settFact(new BigDecimal(tFact).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                        tempRow.settPlan(new BigDecimal(tPlan).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue());
                    }
                }

                busyDlg.setDescription("Заполнение массива данных");
                HashSet<String> tempSetup, tempSector, tempBlock, tempSubBlock, tempTask;

                for (MatRow row: matHash.values()) {

                    //region Переменные для краткости записи ключей хэшей
                    String task = row.getTask() + " - " + row.getTaskName();
                    String subblock = row.getSubblock();//rs.getString("subblock_code_descr");
                    String block = row.getBlock();//rs.getString("block_code_desc");
                    String sector = row.getSector();//rs.getString("sector_code_desc");
                    String section = row.getSection();//rs.getString("section_code_desc");
                    String actCode = row.getAct_code();//rs.getString("act_code_npz_descr");
                    String projName = row.getProjectName();//rs.getString("proj_name");
                    //endregion

                    //region Создание массивов списка подблоков, блоков, секторов, видов работ
                    hashSetSection.add(section);
                    hashSetWorkType.add(actCode);
                    //endregion

                    tempTask = new HashSet<>();
                    tempSubBlock = new HashSet<>();
                    tempBlock = new HashSet<>();
                    tempSector = new HashSet<>();
                    tempSetup = new HashSet<>();

                    //region Создание связей Подблок-Блок / Сектор-Блок / Секций-Сектор
                    if (subblock != null) {
                        if (blockSubBlock.get(block) != null) {
                            for (String str : blockSubBlock.get(block)) {
                                tempSubBlock.add(str);
                            }
                            tempSubBlock.add(subblock);
                            blockSubBlock.put(block, tempSubBlock);
                        } else {
                            tempSubBlock.add(subblock);
                            blockSubBlock.put(block, tempSubBlock);
                        }
                        if (type.equals("normalTask")) {
                            if (blockTask.get(subblock) != null) {
                                for (String str : blockTask.get(subblock)) {
                                    tempTask.add(str);
                                }
                                tempTask.add(task);
                                blockTask.put(subblock, tempTask);
                            } else {
                                tempTask.add(task);
                                blockTask.put(subblock, tempTask);
                            }
                        }
                    } else {
                        if (block != null && type.equals("normalTask")) {
                            if (blockTask.get(block) != null) {
                                for (String str : blockTask.get(block)) {
                                    tempTask.add(str);
                                }
                                tempTask.add(task);
                                blockTask.put(block, tempTask);
                            } else {
                                tempTask.add(task);
                                blockTask.put(block, tempTask);
                            }
                        }
                    }

                    if (block != null) {
                        if (sectorBlock.get(sector) != null) {
                            for (String str : sectorBlock.get(sector)) {
                                tempBlock.add(str);
                            }
                            tempBlock.add(block);
                            sectorBlock.put(sector, tempBlock);
                        } else {
                            tempBlock.add(block);
                            sectorBlock.put(sector, tempBlock);
                        }
                    }

                    if (sector != null) {
                        if (setupSector.get(projName) != null) {
                            for (String str : setupSector.get(projName)) {
                                tempSector.add(str);
                            }
                            tempSector.add(sector);
                            setupSector.put(projName, tempSector);
                        } else {
                            tempSector.add(sector);
                            setupSector.put(projName, tempSector);
                        }
                    } else {
                        errorList.add(rs.getString("task_code"));
                    }

                    if (projName != null) {
                        if (sectionSetup.get(section) != null) {
                            for (String str : sectionSetup.get(section)) {
                                tempSetup.add(str);
                            }
                            tempSetup.add(projName);
                            sectionSetup.put(section, tempSetup);
                        } else {
                            tempSetup.add(projName);
                            sectionSetup.put(section, tempSetup);
                        }
                    }
                    //endregion

                    //region Заполнение массивов данных
                    if (type.equals("normalTask")) {
                        if (allValuesTask.containsKey(task + actCode)) {
                            allValuesTask.put(task + actCode + "pv_plan", row.getPvPlan() + allValuesTask.get(task + actCode + "pv_plan"));
                            allValuesTask.put(task + actCode + "pv_fact", row.getPvFact() + allValuesTask.get(task + actCode + "pv_fact"));
                            allValuesTask.put(task + actCode + "t_plan", row.gettPlan() + allValuesTask.get(task + actCode + "t_plan"));
                            allValuesTask.put(task + actCode + "t_fact", row.gettFact() + allValuesTask.get(task + actCode + "t_fact"));

                            allValuesTask.put(task + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesTask.get(task + actCode + "pv_plan_virtual"));
                            allValuesTask.put(task + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesTask.get(task + actCode + "pv_fact_virtual"));
                            allValuesTask.put(task + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesTask.get(task + actCode + "t_plan_virtual"));
                            allValuesTask.put(task + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesTask.get(task + actCode + "t_fact_virtual"));
                        } else {
                            allValuesTask.put(task + actCode, 0.00);
                            allValuesTask.put(task + actCode + "pv_plan", row.getPvPlan());
                            allValuesTask.put(task + actCode + "pv_fact", row.getPvFact());
                            allValuesTask.put(task + actCode + "t_plan", row.gettPlan());
                            allValuesTask.put(task + actCode + "t_fact", row.gettFact());

                            allValuesTask.put(task + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                            allValuesTask.put(task + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                            allValuesTask.put(task + actCode + "t_plan_virtual", row.gettPlanVirtual());
                            allValuesTask.put(task + actCode + "t_fact_virtual", row.gettFactVirtual());
                        }
                    }

                    if (allValuesSubBlock.containsKey(subblock + actCode)) {
                        allValuesSubBlock.put(subblock + actCode + "pv_plan", row.getPvPlan() + allValuesSubBlock.get(subblock + actCode + "pv_plan"));
                        allValuesSubBlock.put(subblock + actCode + "pv_fact", row.getPvFact() + allValuesSubBlock.get(subblock + actCode + "pv_fact"));
                        allValuesSubBlock.put(subblock + actCode + "t_plan", row.gettPlan() + allValuesSubBlock.get(subblock + actCode + "t_plan"));
                        allValuesSubBlock.put(subblock + actCode + "t_fact", row.gettFact() + allValuesSubBlock.get(subblock + actCode + "t_fact"));

                        allValuesSubBlock.put(subblock + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesSubBlock.get(subblock + actCode + "pv_plan_virtual"));
                        allValuesSubBlock.put(subblock + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesSubBlock.get(subblock + actCode + "pv_fact_virtual"));
                        allValuesSubBlock.put(subblock + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesSubBlock.get(subblock + actCode + "t_plan_virtual"));
                        allValuesSubBlock.put(subblock + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesSubBlock.get(subblock + actCode + "t_fact_virtual"));
                    } else {
                        allValuesSubBlock.put(subblock + actCode, 0.00);
                        allValuesSubBlock.put(subblock + actCode + "pv_plan", row.getPvPlan());
                        allValuesSubBlock.put(subblock + actCode + "pv_fact", row.getPvFact());
                        allValuesSubBlock.put(subblock + actCode + "t_plan", row.gettPlan());
                        allValuesSubBlock.put(subblock + actCode + "t_fact", row.gettFact());

                        allValuesSubBlock.put(subblock + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                        allValuesSubBlock.put(subblock + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                        allValuesSubBlock.put(subblock + actCode + "t_plan_virtual", row.gettPlanVirtual());
                        allValuesSubBlock.put(subblock + actCode + "t_fact_virtual", row.gettFactVirtual());
                    }

                    if (allValuesBlock.containsKey(block + actCode)) {
                        allValuesBlock.put(block + actCode + "pv_plan", row.getPvPlan() + allValuesBlock.get(block + actCode + "pv_plan"));
                        allValuesBlock.put(block + actCode + "pv_fact", row.getPvFact() + allValuesBlock.get(block + actCode + "pv_fact"));
                        allValuesBlock.put(block + actCode + "t_plan", row.gettPlan() + allValuesBlock.get(block + actCode + "t_plan"));
                        allValuesBlock.put(block + actCode + "t_fact", row.gettFact() + allValuesBlock.get(block + actCode + "t_fact"));

                        allValuesBlock.put(block + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesBlock.get(block + actCode + "pv_plan_virtual"));
                        allValuesBlock.put(block + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesBlock.get(block + actCode + "pv_fact_virtual"));
                        allValuesBlock.put(block + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesBlock.get(block + actCode + "t_plan_virtual"));
                        allValuesBlock.put(block + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesBlock.get(block + actCode + "t_fact_virtual"));
                    } else {
                        allValuesBlock.put(block + actCode, 0.00);
                        allValuesBlock.put(block + actCode + "pv_plan", row.getPvPlan());
                        allValuesBlock.put(block + actCode + "pv_fact", row.getPvFact());
                        allValuesBlock.put(block + actCode + "t_plan", row.gettPlan());
                        allValuesBlock.put(block + actCode + "t_fact", row.gettFact());

                        allValuesBlock.put(block + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                        allValuesBlock.put(block + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                        allValuesBlock.put(block + actCode + "t_plan_virtual", row.gettPlanVirtual());
                        allValuesBlock.put(block + actCode + "t_fact_virtual", row.gettFactVirtual());
                    }

                    if (allValuesSector.containsKey(sector + actCode)) {
                        allValuesSector.put(sector + actCode + "pv_plan", row.getPvPlan() + allValuesSector.get(sector + actCode + "pv_plan"));
                        allValuesSector.put(sector + actCode + "pv_fact", row.getPvFact() + allValuesSector.get(sector + actCode + "pv_fact"));
                        allValuesSector.put(sector + actCode + "t_plan", row.gettPlan() + allValuesSector.get(sector + actCode + "t_plan"));
                        allValuesSector.put(sector + actCode + "t_fact", row.gettFact() + allValuesSector.get(sector + actCode + "t_fact"));

                        allValuesSector.put(sector + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesSector.get(sector + actCode + "pv_plan_virtual"));
                        allValuesSector.put(sector + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesSector.get(sector + actCode + "pv_fact_virtual"));
                        allValuesSector.put(sector + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesSector.get(sector + actCode + "t_plan_virtual"));
                        allValuesSector.put(sector + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesSector.get(sector + actCode + "t_fact_virtual"));
                    } else {
                        allValuesSector.put(sector + actCode, 0.00);
                        allValuesSector.put(sector + actCode + "pv_plan", row.getPvPlan());
                        allValuesSector.put(sector + actCode + "pv_fact", row.getPvFact());
                        allValuesSector.put(sector + actCode + "t_plan", row.gettPlan());
                        allValuesSector.put(sector + actCode + "t_fact", row.gettFact());

                        allValuesSector.put(sector + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                        allValuesSector.put(sector + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                        allValuesSector.put(sector + actCode + "t_plan_virtual", row.gettPlanVirtual());
                        allValuesSector.put(sector + actCode + "t_fact_virtual", row.gettFactVirtual());
                    }

                    if (allValuesSection.containsKey(section + actCode)) {
                        allValuesSection.put(section + actCode + "pv_plan", row.getPvPlan() + allValuesSection.get(section + actCode + "pv_plan"));
                        allValuesSection.put(section + actCode + "pv_fact", row.getPvFact() + allValuesSection.get(section + actCode + "pv_fact"));
                        allValuesSection.put(section + actCode + "t_plan", row.gettPlan() + allValuesSection.get(section + actCode + "t_plan"));
                        allValuesSection.put(section + actCode + "t_fact", row.gettFact() + allValuesSection.get(section + actCode + "t_fact"));

                        allValuesSection.put(section + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesSection.get(section + actCode + "pv_plan_virtual"));
                        allValuesSection.put(section + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesSection.get(section + actCode + "pv_fact_virtual"));
                        allValuesSection.put(section + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesSection.get(section + actCode + "t_plan_virtual"));
                        allValuesSection.put(section + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesSection.get(section + actCode + "t_fact_virtual"));
                    } else {
                        allValuesSection.put(section + actCode, 0.00);
                        allValuesSection.put(section + actCode + "pv_plan", row.getPvPlan());
                        allValuesSection.put(section + actCode + "pv_fact", row.getPvFact());
                        allValuesSection.put(section + actCode + "t_plan", row.gettPlan());
                        allValuesSection.put(section + actCode + "t_fact", row.gettFact());

                        allValuesSection.put(section + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                        allValuesSection.put(section + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                        allValuesSection.put(section + actCode + "t_plan_virtual", row.gettPlanVirtual());
                        allValuesSection.put(section + actCode + "t_fact_virtual", row.gettFactVirtual());
                    }

                    if (allValuesSetup.containsKey(projName + actCode)) {
                        allValuesSetup.put(projName + actCode + "pv_plan", row.getPvPlan() + allValuesSetup.get(projName + actCode + "pv_plan"));
                        allValuesSetup.put(projName + actCode + "pv_fact", row.getPvFact() + allValuesSetup.get(projName + actCode + "pv_fact"));
                        allValuesSetup.put(projName + actCode + "t_plan", row.gettPlan() + allValuesSetup.get(projName + actCode + "t_plan"));
                        allValuesSetup.put(projName + actCode + "t_fact", row.gettFact() + allValuesSetup.get(projName + actCode + "t_fact"));

                        allValuesSetup.put(projName + actCode + "pv_plan_virtual", row.getPvPlanVirtual() + allValuesSetup.get(projName + actCode + "pv_plan_virtual"));
                        allValuesSetup.put(projName + actCode + "pv_fact_virtual", row.getPvFactVirtual() + allValuesSetup.get(projName + actCode + "pv_fact_virtual"));
                        allValuesSetup.put(projName + actCode + "t_plan_virtual", row.gettPlanVirtual() + allValuesSetup.get(projName + actCode + "t_plan_virtual"));
                        allValuesSetup.put(projName + actCode + "t_fact_virtual", row.gettFactVirtual() + allValuesSetup.get(projName + actCode + "t_fact_virtual"));

                    } else {
                        allValuesSetup.put(projName + actCode, 0.00);
                        allValuesSetup.put(projName + actCode + "pv_plan", row.getPvPlan());
                        allValuesSetup.put(projName + actCode + "pv_fact", row.getPvFact());
                        allValuesSetup.put(projName + actCode + "t_plan", row.gettPlan());
                        allValuesSetup.put(projName + actCode + "t_fact", row.gettFact());

                        allValuesSetup.put(projName + actCode + "pv_plan_virtual", row.getPvPlanVirtual());
                        allValuesSetup.put(projName + actCode + "pv_fact_virtual", row.getPvFactVirtual());
                        allValuesSetup.put(projName + actCode + "t_plan_virtual", row.gettPlanVirtual());
                        allValuesSetup.put(projName + actCode + "t_fact_virtual", row.gettFactVirtual());
                    }

                    if (row.gettPlanVirtual() > 0 ) {

                        allValuesTask.put(task + actCode + "t_virtual", 1.0);
                        allValuesSubBlock.put(subblock + actCode + "t_virtual", 1.0);
                        allValuesBlock.put(block + actCode + "t_virtual", 1.0);
                        allValuesSector.put(sector + actCode + "t_virtual", 1.0);
                        allValuesSection.put(section + actCode + "t_virtual", 1.0);
                        allValuesSetup.put(projName + actCode + "t_virtual", 1.0);
                    }

                    if (row.getPvPlanVirtual() > 0 ) {

                        allValuesTask.put(task + actCode + "pv_virtual", 1.0);
                        allValuesSubBlock.put(subblock + actCode + "pv_virtual", 1.0);
                        allValuesBlock.put(block + actCode + "pv_virtual", 1.0);
                        allValuesSector.put(sector + actCode + "pv_virtual", 1.0);
                        allValuesSection.put(section + actCode + "pv_virtual", 1.0);
                        allValuesSetup.put(projName + actCode + "pv_virtual", 1.0);

                        allValuesTask.put(task + actCode + "virtual", 1.0);
                        allValuesSubBlock.put(subblock + actCode + "virtual", 1.0);
                        allValuesBlock.put(block + actCode + "virtual", 1.0);
                        allValuesSector.put(sector + actCode + "virtual", 1.0);
                        allValuesSection.put(section + actCode + "virtual", 1.0);
                        allValuesSetup.put(projName + actCode + "virtual", 1.0);
                    }
                    //endregion
                }
                rs.close();
                stmt.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        workType = new ArrayList<>(hashSetWorkType);
        Collections.sort(workType, new Comparator<String>() {
            public int compare(String o1, String o2) {
                return o1.compareTo(o2);
            }
        });
    }
    //endregion

    //region Создание книги EXCEL, листа, запуск генерации отчёта и запись получившийся книги
    private void createReport() {
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("Лист1");
        rownum = 0;

        sheet.setRowSumsBelow(false);

        createStyles();
        addTitle();

        ArrayList<String> sectionList = new ArrayList<>(hashSetSection);
        Collections.sort(sectionList, new Comparator<String>() {
            public int compare(String o1, String o2) {
                return o1.compareTo(o2);
            }
        });
        for (String sectionName : sectionList) {
            try {
                addSection(sectionName);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        sheet.setColumnWidth(0, 256 * 51);
        if (curReport == 2) {
            int j = 0;
            for (int i = 1; i < workType.size() * 3 + 4; i++) {
                if (j < 2) {
                    j++;
                    sheet.setColumnWidth(i, 256 * 10);
                } else {
                    j = 0;
                    sheet.setColumnWidth(i, 256 * 11);
                }
            }
        } else {
            for (int i = 1; i < workType.size() * 2 + 1; i++) {
                sheet.setColumnWidth(i, 256 * 9);
            }
        }

        FileOutputStream out;
        try {
            busyDlg.setDescription("Запись" + curReport + " отчёта");
            out = new FileOutputStream(new File(pathXLSFile + "(Отчет " + curReport + ").xls"));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    //endregion

    //region Добавление шапки таблицы
    private void addTitle() {
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(147.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellValue("Наименование здания или сооружения");
        cell.setCellStyle(styles.get("name_build_or_setup"));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);
            cell.setCellValue(rn);
            cell.setCellStyle(styles.get("rn_style"));
            if (curReport == 2) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 2));
                i += 3;
            } else {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 1));
                i += 2;
            }
        }
        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue("Итог Т-ты факт.");
            cell.setCellStyle(styles.get("rn_style"));
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
            i++;
            cell = row.createCell(i);
            cell.setCellValue("Итог Т-ты план.");
            cell.setCellStyle(styles.get("rn_style"));
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
            i++;
            cell = row.createCell(i);
            cell.setCellValue("Итог Доля т-т план.");
            cell.setCellStyle(styles.get("rn_style"));
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
        }
        Row row2 = sheet.createRow(rownum++);
        row2.setHeightInPoints(32.00f);
        if (curReport == 3) {
            for (int j = 1; j < workType.size() * 2 + 1; j++) {
                if (j % 2 == 0) {
                    cell = row2.createCell(j);
                    cell.setCellValue("ФО факт.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                } else {
                    cell = row2.createCell(j);
                    cell.setCellValue("ФО план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                }
            }
        } else if (curReport == 1) {
            for (int j = 1; j < workType.size() * 2 + 1; j++) {
                if (j % 2 == 0) {
                    cell = row2.createCell(j);
                    cell.setCellValue("Т-ты план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                } else {
                    cell = row2.createCell(j);
                    cell.setCellValue("ФО план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                }
            }
        } else if (curReport == 2) {
            int check = 0;
            for (int j = 1; j < workType.size() * 3 + 1; j++) {
                check++;
                if (check == 4) {
                    check = 1;
                }
                if (check == 1) {
                    cell = row2.createCell(j);
                    cell.setCellValue("Т-ты факт.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                } else if (check == 2) {
                    cell = row2.createCell(j);
                    cell.setCellValue("Т-ты план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                } else if (check == 3) {
                    cell = row2.createCell(j);
                    cell.setCellValue("Доля т-т план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                }
            }
        }
    }
    //endregion

    //region Добавление стилей таблицы
    private void createStyles() {
        styles = new HashMap<>();
        CellStyle style;

        //region Цветовая палитра
        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short) 57, (byte) 237, (byte) 239, (byte) 175);
        palette.setColorAtIndex((short) 58, (byte) 141, (byte) 180, (byte) 226);
        palette.setColorAtIndex((short) 59, (byte) 191, (byte) 212, (byte) 190);
        palette.setColorAtIndex((short) 60, (byte) 230, (byte) 184, (byte) 183);
        palette.setColorAtIndex((short) 61, (byte) 218, (byte) 238, (byte) 243);
        palette.setColorAtIndex((short) 62, (byte) 220, (byte) 230, (byte) 241);
        //endregion

        //region Шрифты
        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short) 12);
        mainFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        mainFont.setFontName("Times New Roman");

        HSSFFont mainFontRed = wb.createFont();
        mainFontRed.setFontHeightInPoints((short) 12);
        mainFontRed.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        mainFontRed.setFontName("Times New Roman");
        mainFontRed.setColor(HSSFColor.RED.index);

        HSSFFont blockFont = wb.createFont();
        blockFont.setFontHeightInPoints((short) 12);
        blockFont.setFontName("Times New Roman");

        HSSFFont blockFontRed = wb.createFont();
        blockFontRed.setFontHeightInPoints((short) 12);
        blockFontRed.setFontName("Times New Roman");
        blockFontRed.setColor(HSSFColor.RED.index);

        HSSFFont subblockFont = wb.createFont();
        subblockFont.setFontHeightInPoints((short) 12);
        subblockFont.setFontName("Times New Roman");
        subblockFont.setItalic(true);

        HSSFFont subblockFontRed = wb.createFont();
        subblockFontRed.setFontHeightInPoints((short) 12);
        subblockFontRed.setFontName("Times New Roman");
        subblockFontRed.setItalic(true);
        subblockFontRed.setColor(HSSFColor.RED.index);
        //endregion

        //region Стили для ячеек

        //region name_build_or_setup - стиль ячейки 1.1 (Огромная зелёная херня)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("name_build_or_setup", style);
        //endregion

        //region rn_style - Стиль первой строки "Вид работ"
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(mainFont);
        style.setRotation((short) 90);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_MEDIUM);
        style.setLeftBorderColor(HSSFColor.BLACK.index);

        styles.put("rn_style", style);
        //endregion

        //region under_rn_style - Стиль второй строки значения факт/план и т.д.
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(mainFont);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(62).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_MEDIUM);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        styles.put("under_rn_style", style);
        //endregion

        //region section_left - Название секции
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("section_left", style);
        //endregion

        //region section_right_1 - первый столбец значений в строке секции
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_1", style);
        //endregion

        //region section_right_2 - второй/третий столбец значений в строке секции(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_2", style);
        //endregion

        //region section_right_3 - второй столбец значений в строке секции(тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_3", style);
        //endregion

        //region section_right_1_red - первый столбец значений в строке секции
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_1_red", style);
        //endregion

        //region section_right_2_red - второй/третий столбец значений в строке секции(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_2_red", style);
        //endregion

        //region section_right_3_red - второй столбец значений в строке секции(тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_3_red", style);
        //endregion

        //region section_right_per - третий столбец значений в строке секции для процентов
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("section_right_per", style);
        //endregion

        //region setup_left - Название установки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_left", style);
        //endregion

        //region setup_right_1 - первый столбец значений в строке установки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_1", style);
        //endregion

        //region setup_right_2 - второй/третий столбец значений в строке установки(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_2", style);
        //endregion

        //region setup_right_3 - второй столбец значений в строке установки(тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_3", style);
        //endregion

        //region setup_right_1_red - первый столбец значений в строке установки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_1_red", style);
        //endregion

        //region setup_right_2_red - второй/третий столбец значений в строке установки(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_2_red", style);
        //endregion

        //region setup_right_3_red - второй столбец значений в строке установки(тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_3_red", style);
        //endregion

        //region setup_right_per - третий столбец значений в строке установки для процентов
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_per", style);
        //endregion

        //region sector_left - Название сектора
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_left", style);
        //endregion

        //region sector_right_1 - первый столбец значений в строке сектора
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_1", style);
        //endregion

        //region sector_right_2 - второй/третий столбец значений в строке сектора(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_2", style);
        //endregion

        //region sector_right_3 - второй столбец значений в строке сектора (тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_3", style);
        //endregion

        //region sector_right_1_red - первый столбец значений в строке сектора
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_1_red", style);
        //endregion

        //region sector_right_2_red - второй/третий столбец значений в строке сектора(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_2_red", style);
        //endregion

        //region sector_right_3_red - второй столбец значений в строке сектора (тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_3_red", style);
        //endregion

        //region sector_right_per - третий столбец значений в строке сектора для процентов
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_per", style);
        //endregion

        //region block_left - Название блока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("block_left", style);
        //endregion

        //region block_right_1 - первый столбец значений в строке блока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_1", style);
        //endregion

        //region block_right_1_red - первый столбец значений в строке блока красный шрифт
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_1_red", style);
        //endregion

        //region block_right_2 - второй/третий столбец значений в строке блока (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_2", style);
        //endregion

        //region block_right_2_red - второй/третий столбец значений в строке блока (жирная правая граница) красный шрифт
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_2_red", style);
        //endregion

        //region block_right_3 - второй столбец значений в строке блока (тонкая левая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_3", style);
        //endregion

        //region block_right_3_red - второй столбец значений в строке блока (тонкая левая граница) красный шрифт
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_3_red", style);
        //endregion

        //region block_right_per - третий столбец значений в строке блока для процентов
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_per", style);
        //endregion

        //region sub_block_left - Название подблока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_left", style);
        //endregion

        //region sub_block_right_1 - первый столбец значений в строке подблока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_1", style);
        //endregion

        //region sub_block_right_2 - второй/третий столбец значений в строке подблока (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_2", style);
        //endregion

        //region sub_block_right_3 - второй столбец значений в строке подблока (тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_3", style);
        //endregion

        //region sub_block_right_1_red - первый столбец значений в строке подблока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_1_red", style);
        //endregion

        //region sub_block_right_2_red - второй/третий столбец значений в строке подблока (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_2_red", style);
        //endregion

        //region sub_block_right_3_red - второй столбец значений в строке подблока (тонкая правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFontRed);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_3_red", style);
        //endregion

        //region sub_block_right_per - третий столбец значений в строке подблока для процентов
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_per", style);
        //endregion

        //endregion
    }
    //endregion

    private void addSection(String sectionName) throws SQLException {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(sectionName);
        cell.setCellStyle(styles.get("section_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesSection.get(sectionName + rn + "t_plan") != null) {
                totalTTFact += allValuesSection.get(sectionName + rn + "t_fact");
                totalForPercent += allValuesSection.get(sectionName + rn + "t_plan");
            }
            if (allValuesSection.get(sectionName + rn + "t_plan_virtual") != null) {
                totalTTFact += allValuesSection.get(sectionName + rn + "t_fact_virtual");
                totalForPercent += allValuesSection.get(sectionName + rn + "t_plan_virtual");
            }
        }

        for (String rn : workType) {
            boolean red1 = false;
            boolean red2 = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSection.get(sectionName + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSection.get(sectionName + rn + "pv_plan"));
                    if (allValuesSection.containsKey(sectionName + rn + "pv_virtual")) {
                        red1 = true;
                        cell.setCellValue(allValuesSection.get(sectionName + rn + "pv_plan") + allValuesSection.get(sectionName + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 2) {
                if (allValuesSection.get(sectionName + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesSection.get(sectionName + rn + "t_fact"));
                    if (allValuesSection.containsKey(sectionName + rn + "t_virtual")) {
                        red1 = true;
                        cell.setCellValue(allValuesSection.get(sectionName + rn + "t_fact") + allValuesSection.get(sectionName + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }
            cell.setCellStyle(styles.get(red1 ? "section_right_1_red" : "section_right_1"));

            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesSection.get(sectionName + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesSection.get(sectionName + rn + "pv_fact"));
                    if (allValuesSection.containsKey(sectionName + rn + "pv_virtual")) {
                        red2 = true;
                        cell.setCellValue(allValuesSection.get(sectionName + rn + "pv_fact") +  allValuesSection.get(sectionName + rn + "pv_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSection.get(sectionName + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSection.get(sectionName + rn + "t_plan"));
                    if (allValuesSection.containsKey(sectionName + rn + "t_virtual")) {
                        red2 = true;
                        cell.setCellValue(allValuesSection.get(sectionName + rn + "t_plan") + allValuesSection.get(sectionName + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get(red2 ? "section_right_3_red" : "section_right_3"));
            } else {
                cell.setCellStyle(styles.get(red2 ? "section_right_2_red" : "section_right_2"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSection.get(sectionName + rn + "t_plan") != null) {
                    cell.setCellValue((allValuesSection.get(sectionName + rn + "t_plan") + allValuesSection.get(sectionName + rn + "t_plan_virtual") )/ totalForPercent);
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("section_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("section_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("section_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("section_right_per"));
        }

        if (sectionSetup.get(sectionName) != null) {
            int groupStart = rownum;
            ArrayList<String> sectionSetupList = new ArrayList<>(sectionSetup.get(sectionName));
            Collections.sort(sectionSetupList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });
            for (String setup : sectionSetupList) {
                addSetup(setup);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addSetup(String setupName) throws SQLException {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(setupName);
        cell.setCellStyle(styles.get("setup_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesSetup.get(setupName + rn + "t_plan") != null) {
                totalTTFact += allValuesSetup.get(setupName + rn + "t_fact");
                totalForPercent += allValuesSetup.get(setupName + rn + "t_plan");
            }

            if (allValuesSetup.get(setupName + rn + "t_plan_virtual") != null) {
                totalTTFact += allValuesSetup.get(setupName + rn + "t_fact_virtual");
                totalForPercent += allValuesSetup.get(setupName + rn + "t_plan_virtual");
            }

        }

        for (String rn : workType) {
            boolean red = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSetup.get(setupName + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSetup.get(setupName + rn + "pv_plan"));
                    if (allValuesSetup.containsKey(setupName + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSetup.get(setupName + rn + "pv_plan") + allValuesSetup.get(setupName + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 2) {
                if (allValuesSetup.get(setupName + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesSetup.get(setupName + rn + "t_fact"));
                    if (allValuesSetup.containsKey(setupName + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSetup.get(setupName + rn + "t_fact") + allValuesSetup.get(setupName + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get((red ? "setup_right_1_red" : "setup_right_1" )));

            i++;
            cell = row.createCell(i);
            red = false;
            if (curReport == 3) {
                if (allValuesSetup.get(setupName + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesSetup.get(setupName + rn + "pv_fact"));
                    if (allValuesSetup.containsKey(setupName + rn + "pv_virtual")) {
                        cell.setCellValue(allValuesSetup.get(setupName + rn + "pv_fact") + allValuesSetup.get(setupName + rn + "pv_fact_virtual"));
                        red = true;
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSetup.get(setupName + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSetup.get(setupName + rn + "t_plan"));
                    if (allValuesSetup.containsKey(setupName + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSetup.get(setupName + rn + "t_plan") + allValuesSetup.get(setupName + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get(red ? "setup_right_3_red" : "setup_right_3"));
            } else {
                cell.setCellStyle(styles.get(red ? "setup_right_2_red" : "setup_right_2"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSetup.get(setupName + rn + "t_plan") != null) {
                    cell.setCellValue((allValuesSetup.get(setupName + rn + "t_plan") + allValuesSetup.get(setupName + rn + "t_plan_virtual") )/ totalForPercent);
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("setup_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("setup_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("setup_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("setup_right_per"));
        }

        if (setupSector.get(setupName) != null) {
            int groupStart = rownum;
            ArrayList<String> sectionSectorList = new ArrayList<>(setupSector.get(setupName));
            Collections.sort(sectionSectorList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });
            for (String sector : sectionSectorList) {
                addSector(sector);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addSector(String sector) throws SQLException {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(sector);
        cell.setCellStyle(styles.get("sector_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesSector.get(sector + rn + "t_plan") != null) {
                totalForPercent += allValuesSector.get(sector + rn + "t_plan");
                totalTTFact += allValuesSector.get(sector + rn + "t_fact");
            }

            if (allValuesSector.get(sector + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesSector.get(sector + rn + "t_plan_virtual");
                totalTTFact += allValuesSector.get(sector + rn + "t_fact_virtual");
            }

        }

        for (String rn : workType) {
            boolean red = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSector.get(sector + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "pv_plan"));
                    if (allValuesSector.containsKey(sector + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSector.get(sector + rn + "pv_plan") + allValuesSector.get(sector + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 2) {
                if (allValuesSector.get(sector + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "t_fact"));
                    if (allValuesSector.containsKey(sector + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSector.get(sector + rn + "t_fact")+allValuesSector.get(sector + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get((red ? "sector_right_1_red" : "sector_right_1")));
            i++;

            cell = row.createCell(i);
            red = false;
            if (curReport == 3) {
                if (allValuesSector.get(sector + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "pv_fact"));
                    if (allValuesSector.containsKey(sector + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSector.get(sector + rn + "pv_fact") + allValuesSector.get(sector + rn + "pv_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSector.get(sector + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "t_plan"));
                    if (allValuesSector.containsKey(sector + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSector.get(sector + rn + "t_plan") + allValuesSector.get(sector + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }
            if (curReport == 2) {
                cell.setCellStyle(styles.get((red ? "sector_right_3_red" : "sector_right_3")));
            } else {
                cell.setCellStyle(styles.get((red ? "sector_right_2_red" : "sector_right_2")));
            }

            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSector.get(sector + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue((allValuesSector.get(sector + rn + "t_plan") + allValuesSector.get(sector + rn + "t_plan_virtual"))/ totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }
                cell.setCellStyle(styles.get("sector_right_per"));
                i++;
            }
        }
        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sector_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sector_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sector_right_per"));
        }

        if (sectorBlock.get(sector) != null) {
            int groupStart = rownum;
            ArrayList<String> sectorBlockList = new ArrayList<>(sectorBlock.get(sector));
            Collections.sort(sectorBlockList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });
            for (String block : sectorBlockList) {
                addBlock(block);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addBlock(String block) {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(block);
        cell.setCellStyle(styles.get("block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        boolean hasVirtual = false;
        for (String rn : workType) {
            if (allValuesBlock.get(block + rn + "t_plan") != null) {
                totalForPercent += allValuesBlock.get(block + rn + "t_plan");
                totalTTFact += allValuesBlock.get(block + rn + "t_fact");
            }

            if (allValuesBlock.get(block + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesBlock.get(block + rn + "t_plan_virtual");
                totalTTFact += allValuesBlock.get(block + rn + "t_fact_virtual");
                hasVirtual = true;
            }
        }

        for (String rn : workType) {
            boolean red = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesBlock.get(block + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_plan"));
                    if (allValuesBlock.containsKey(block + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesBlock.get(block + rn + "pv_plan") + allValuesBlock.get(block + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesBlock.get(block + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_fact"));
                    if (allValuesBlock.containsKey(block + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesBlock.get(block + rn + "t_fact") + allValuesBlock.get(block + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get((red ? "block_right_1_red" : "block_right_1")));
            i++;
            cell = row.createCell(i);
            red = false;
            if (curReport == 3) {
                if (allValuesBlock.get(block + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_fact"));
                    if (allValuesBlock.containsKey(block + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesBlock.get(block + rn + "pv_fact") + allValuesBlock.get(block + rn + "pv_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesBlock.get(block + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_plan"));
                    if (allValuesBlock.containsKey(block + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesBlock.get(block + rn + "t_plan") + allValuesBlock.get(block + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get((red ? "block_right_3_red" : "block_right_3")));
            } else {
                cell.setCellStyle(styles.get((red ? "block_right_2_red" : "block_right_2")));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesBlock.get(block + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue((allValuesBlock.get(block + rn + "t_plan") + allValuesBlock.get(block + rn + "t_plan_virtual")) / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("block_right_per"));
        }

        if (blockSubBlock.get(block) != null) {
            int groupStart = rownum;
            ArrayList<String> blockSunBlockList = new ArrayList<>(blockSubBlock.get(block));
            Collections.sort(blockSunBlockList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            for (String subBlock : blockSunBlockList) {
                addSubBlock(subBlock);
            }
            sheet.groupRow(groupStart, rownum);
        } else if (type.equals("normalTask") && blockTask.get(block) != null) {
            int groupStart = rownum;
            ArrayList<String> blockTaskList = new ArrayList<>(blockTask.get(block));
            Collections.sort(blockTaskList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            for (String task : blockTaskList) {
                addTask(task);
            }
            sheet.groupRow(groupStart, rownum);
        } else if (hasVirtual){
            int groupStart = rownum;
            addBlockSmetTypes(block);
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addSubBlock(String subBlock) {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(subBlock);
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        boolean hasVirtual = false;
        for (String rn : workType) {
            if (allValuesSubBlock.get(subBlock + rn + "t_plan") != null) {
                totalForPercent += allValuesSubBlock.get(subBlock + rn + "t_plan");
                totalTTFact += allValuesSubBlock.get(subBlock + rn + "t_fact");
            }

            if (allValuesSubBlock.get(subBlock + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesSubBlock.get(subBlock + rn + "t_plan_virtual");
                totalTTFact += allValuesSubBlock.get(subBlock + rn + "t_fact_virtual");
                hasVirtual = true;
            }
        }

        for (String rn : workType) {
            boolean red = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSubBlock.get(subBlock + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "pv_plan"));
                    if (allValuesSubBlock.containsKey(subBlock + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "pv_plan") + allValuesSubBlock.get(subBlock + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesSubBlock.get(subBlock + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "t_fact"));
                    if (allValuesSubBlock.containsKey(subBlock + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "t_fact") + allValuesSubBlock.get(subBlock + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get((red ? "sub_block_right_1_red" : "sub_block_right_1")));
            i++;
            cell = row.createCell(i);
            red = false;
            if (curReport == 3) {
                if (allValuesSubBlock.get(subBlock + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "pv_fact"));
                    if (allValuesSubBlock.containsKey(subBlock + rn + "pv_virtual")) {
                        cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "pv_fact") + allValuesSubBlock.get(subBlock + rn + "pv_fact_virtual"));
                        red = true;
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSubBlock.get(subBlock + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "t_plan"));
                    if (allValuesSubBlock.containsKey(subBlock + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "t_plan") + allValuesSubBlock.get(subBlock + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get((red ? "sub_block_right_3_red" : "sub_block_right_3")));
            } else {
                cell.setCellStyle(styles.get((red ? "sub_block_right_2_red" : "sub_block_right_2")));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSubBlock.get(subBlock + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue((allValuesSubBlock.get(subBlock + rn + "t_plan") + allValuesSubBlock.get(subBlock + rn + "t_plan_virtual")) / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }

        if (type.equals("normalTask") && blockTask.get(subBlock) != null) {
            int groupStart = rownum;
            ArrayList<String> subBlockTaskList = new ArrayList<>(blockTask.get(subBlock));
            Collections.sort(subBlockTaskList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            for (String task : subBlockTaskList) {
                addTask(task);
            }
            sheet.groupRow(groupStart, rownum);
        } else if (hasVirtual){
            int groupStart = rownum;
            addSubBlockSmetTypes(subBlock);
            sheet.groupRow(groupStart, rownum);
        }

    }

    private void addBlockSmetTypes(String block) {
        Cell cell;

        //region Добавление строки показателей реальной сметы
        Row row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Реальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesBlock.get(block + rn + "t_plan") != null) {
                totalForPercent += allValuesBlock.get(block + rn + "t_plan");
                totalTTFact += allValuesBlock.get(block + rn + "t_fact");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesBlock.get(block + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesBlock.get(block + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_fact"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesBlock.get(block + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_fact"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesBlock.get(block + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesBlock.get(block + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesBlock.get(block + rn + "t_plan") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

        //region Добавление строки показателей виртуальной сметы
        row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Виртуальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        i = 1;

        totalForPercent = 0;
        totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesBlock.get(block + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesBlock.get(block + rn + "t_plan_virtual");
                totalTTFact += allValuesBlock.get(block + rn + "t_fact_virtual");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesBlock.get(block + rn + "pv_plan_virtual") != null && allValuesBlock.get(block + rn + "pv_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesBlock.get(block + rn + "t_fact_virtual") != null && allValuesBlock.get(block + rn + "t_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesBlock.get(block + rn + "pv_fact_virtual") != null && allValuesBlock.get(block + rn + "pv_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesBlock.get(block + rn + "t_plan_virtual") != null && allValuesBlock.get(block + rn + "t_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3_red"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2_red"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesBlock.get(block + rn + "t_plan_virtual") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesBlock.get(block + rn + "t_plan_virtual") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

    }

    private void addSubBlockSmetTypes(String block) {
        Cell cell;

        //region Добавление строки показателей реальной сметы
        Row row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Реальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesSubBlock.get(block + rn + "t_plan") != null) {
                totalForPercent += allValuesSubBlock.get(block + rn + "t_plan");
                totalTTFact += allValuesSubBlock.get(block + rn + "t_fact");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSubBlock.get(block + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesSubBlock.get(block + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "t_fact"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesSubBlock.get(block + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "pv_fact"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSubBlock.get(block + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSubBlock.get(block + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesSubBlock.get(block + rn + "t_plan") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

        //region Добавление строки показателей виртуальной сметы
        row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Виртуальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        i = 1;

        totalForPercent = 0;
        totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesSubBlock.get(block + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesSubBlock.get(block + rn + "t_plan_virtual");
                totalTTFact += allValuesSubBlock.get(block + rn + "t_fact_virtual");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesSubBlock.get(block + rn + "pv_plan_virtual") != null && allValuesSubBlock.get(block + rn + "pv_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "pv_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesSubBlock.get(block + rn + "t_fact_virtual") != null && allValuesSubBlock.get(block + rn + "t_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "t_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesSubBlock.get(block + rn + "pv_fact_virtual") != null && allValuesSubBlock.get(block + rn + "pv_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "pv_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesSubBlock.get(block + rn + "t_plan_virtual") != null && allValuesSubBlock.get(block + rn + "t_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesSubBlock.get(block + rn + "t_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3_red"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2_red"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesSubBlock.get(block + rn + "t_plan_virtual") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesSubBlock.get(block + rn + "t_plan_virtual") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

    }

    private void addTask(String task) {
        Row row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(task);
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        boolean hasVirtual = false;
        for (String rn : workType) {
            if (allValuesTask.get(task + rn + "t_plan") != null) {
                totalForPercent += allValuesTask.get(task + rn + "t_plan");
                totalTTFact += allValuesTask.get(task + rn + "t_fact");
            }

            if (allValuesTask.get(task + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesTask.get(task + rn + "t_plan_virtual");
                totalTTFact += allValuesTask.get(task + rn + "t_fact_virtual");
                hasVirtual = true;
            }

        }

        for (String rn : workType) {
            boolean red = false;
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesTask.get(task + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_plan"));
                    if (allValuesTask.containsKey(task + rn + "pv_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesTask.get(task + rn + "pv_plan") + allValuesTask.get(task + rn + "pv_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesTask.get(task + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_fact"));
                    if (allValuesTask.containsKey(task + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesTask.get(task + rn + "t_fact") + allValuesTask.get(task + rn + "t_fact_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get((red ? "block_right_1_red" : "block_right_1")));
            i++;
            cell = row.createCell(i);
            red = false;
            if (curReport == 3) {
                if (allValuesTask.get(task + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_fact"));
                    if (allValuesTask.containsKey(task + rn + "pv_virtual")) {
                        cell.setCellValue(allValuesTask.get(task + rn + "pv_fact") + allValuesTask.get(task + rn + "pv_fact_virtual"));
                        red = true;
                    }
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesTask.get(task + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_plan"));
                    if (allValuesTask.containsKey(task + rn + "t_virtual")) {
                        red = true;
                        cell.setCellValue(allValuesTask.get(task + rn + "t_plan") + allValuesTask.get(task + rn + "t_plan_virtual"));
                    }
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get((red ? "block_right_3_red" : "block_right_3")));
            } else {
                cell.setCellStyle(styles.get((red ? "block_right_2_red" : "block_right_2")));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesTask.get(task + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue((allValuesTask.get(task + rn + "t_plan") + allValuesTask.get(task + rn + "t_plan_virtual")) / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }

        if (hasVirtual){
            int groupStart = rownum;
            addTaskSmetTypes(task);
            sheet.groupRow(groupStart, rownum);
        }

    }

    private void addTaskSmetTypes(String task) {
        Cell cell;

        //region Добавление строки показателей реальной сметы
        Row row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Реальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        double totalForPercent = 0;
        double totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesTask.get(task + rn + "t_plan") != null) {
                totalForPercent += allValuesTask.get(task + rn + "t_plan");
                totalTTFact += allValuesTask.get(task + rn + "t_fact");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesTask.get(task + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesTask.get(task + rn + "t_fact") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_fact"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesTask.get(task + rn + "pv_fact") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_fact"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesTask.get(task + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesTask.get(task + rn + "t_plan") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesTask.get(task + rn + "t_plan") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

        //region Добавление строки показателей виртуальной сметы
        row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue("Виртуальные сметы");
        cell.setCellStyle(styles.get("sub_block_left"));

        i = 1;

        totalForPercent = 0;
        totalTTFact = 0;
        for (String rn : workType) {
            if (allValuesTask.get(task + rn + "t_plan_virtual") != null) {
                totalForPercent += allValuesTask.get(task + rn + "t_plan_virtual");
                totalTTFact += allValuesTask.get(task + rn + "t_fact_virtual");
            }
        }

        for (String rn : workType) {
            cell = row.createCell(i);

            if (curReport == 3 || curReport == 1) {
                if (allValuesTask.get(task + rn + "pv_plan_virtual") != null && allValuesTask.get(task + rn + "pv_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else {
                if (allValuesTask.get(task + rn + "t_fact_virtual") != null && allValuesTask.get(task + rn + "t_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            if (curReport == 3) {
                if (allValuesTask.get(task + rn + "pv_fact_virtual") != null && allValuesTask.get(task + rn + "pv_fact_virtual") != 0.0) {
                    cell.setCellValue(allValuesTask.get(task + rn + "pv_fact_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            } else if (curReport == 1 || curReport == 2) {
                if (allValuesTask.get(task + rn + "t_plan_virtual") != null && allValuesTask.get(task + rn + "t_plan_virtual") != 0.0) {
                    cell.setCellValue(allValuesTask.get(task + rn + "t_plan_virtual"));
                } else {
                    cell.setCellValue("---");
                }
            }

            if (curReport == 2) {
                cell.setCellStyle(styles.get("sub_block_right_3_red"));
            } else {
                cell.setCellStyle(styles.get("sub_block_right_2_red"));
            }
            i++;

            if (curReport == 2) {
                cell = row.createCell(i);
                if (allValuesTask.get(task + rn + "t_plan_virtual") != null) {
                    if (totalForPercent == 0) {
                        cell.setCellValue(0);
                    } else {
                        cell.setCellValue(allValuesTask.get(task + rn + "t_plan_virtual") / totalForPercent);
                    }
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_per"));
                i++;
            }
        }

        if (curReport == 2) {
            cell = row.createCell(i);
            cell.setCellValue(totalTTFact);
            cell.setCellStyle(styles.get("sub_block_right_1_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(totalForPercent);
            cell.setCellStyle(styles.get("sub_block_right_3_red"));
            i++;
            cell = row.createCell(i);
            cell.setCellValue(1);
            cell.setCellStyle(styles.get("sub_block_right_per"));
        }
        //endregion

    }

}
